﻿using MultiLang;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
using System.Windows.Forms;

using BLToolkit.Data.Linq;
using JetBrains.Annotations;
using Rsdn.Janus.CoreServices.Extensibility;
using Rsdn.Janus.DataModel;
using Rsdn.SmartApp;

namespace Rsdn.Janus
{
    public static partial class DatabaseManager
    {
        #region Агрегация данных в локальной БД

        /// <summary>
        /// Обновление аггрегатов
        /// </summary>
        /// <param name="provider">Поставщик сервисов.</param>
        /// <param name="db">Менеджер баз данных.</param>
        /// <param name="tidList">Массив Topic ID для препарации</param>
        /// <param name="selfId"></param>
        public static void UpdateAggregates(
            IServiceProvider provider,
			IDataContext db,
			IEnumerable<int> tidList, 
			int selfId)
        {
            foreach (var series in tidList.SplitForInClause(provider))
            {
                IEnumerable<int> locSeries = series;
                UpdateTopicInfoByFilter(
                    db,
                    ti => locSeries.Contains(ti.MessageID),
                    m => locSeries.Contains(m.ID), selfId);
            }
        }

        /// <summary>
        /// Обновляет агрегатные данные по темам (таблицу topic_info).
        /// Эта процедура сначала удаляет записи соотвествующие параметру filter,
        /// а потом формирует агрегатные данные и добавляет их в topic_info.
        /// Транзакция должна контролироваться вызывающим методом.
        /// </summary>
        /// <param name="db">Менеджер баз данных.</param>
        /// <param name="topicPredicate">комплиментарный <see cref="msgPredicate"/> предикат для TopicInfo</param>
		/// <param name="msgPredicate">Необязательный параметр позволяющий задать
        /// фильтрацию тем данные по которым нужно пересчитать.
        /// Может быть null или string.Empty. Если параметр не задан,
        /// обновляются все сообщения. При этом время обновления может быть
		/// довольно большим (на Athlon 2100+ 512 MB RAM ~ 1 мин.).
        /// Пример: "mid IN (1, 2, 3)" обновит агрегатные значения
        /// для тем 1, 2 и 3.</param>
        /// <param name="slefId"></param>
        /// <returns>
        /// Возвращает количество вставленных записей.
        /// </returns>
        private static int UpdateTopicInfoByFilter(
			IDataContext db,
            [CanBeNull] Expression<Func<ITopicInfo, bool>> topicPredicate,
            [CanBeNull] Expression<Func<IForumMessage, bool>> msgPredicate, 
            int selfId)
        {
#if FRM_UPDATE_TRACE
            Trace.WriteLine(string.Format("FRU: DataBaseManager.UpdateTopicInfoByFilter")); //MLHIDE
#endif
            // Удаляем.
            if (topicPredicate != null)
            {
                db
                    .TopicInfos(topicPredicate)
                    .Delete();
            }
            else
            {
                db.TopicInfos().Delete();
            }

            Table<IRate> rates = db.Rates();
            #region SQL
            //--  SQLite
            //SELECT
            //    [t1].[ID],
            //    [t1].[dte] as [Date1],
            //    [t1].[rate] as [RateType],
            //    [t1].[rby] as [Multiplier],
            //    [t1].[mid] as [MessageID],
            //    [t1].[tid] as [TopicID],
            //    [t1].[uid] as [UserID]
            //FROM
            //    [rating] [t1]
            #endregion
            Table<IModeratorial> mods = db.Moderatorials();
            #region SQL
            //--  SQLite
            //SELECT
            //    [t1].[MessageID],
            //    [t1].[UserID],
            //    [t1].[ForumID],
            //    [t1].[Create] as [Create1]
            //FROM
            //    [moderatorials] [t1]
            #endregion

            IQueryable<IForumMessage> msgs = db.Messages(m => m.TopicID == 0);
            if (msgPredicate != null)
            {
                msgs = msgs.Where(msgPredicate);
                #region SQL
                //--  SQLite
                //SELECT
                //    [m].[mid] as [ID],
                //    [m].[tid] as [TopicID],
                //    [m].[pid] as [ParentID],
                //    [m].[gid] as [ForumID],
                //    [m].[Subject],
                //    [m].[dte] as [Date1],
                //    [m].[uid] as [UserID],
                //    [m].[uclass] as [UserClass],
                //    [m].[UserNick],
                //    [m].[Name],
                //    [m].[article_id] as [ArticleId],
                //    [m].[LastModerated],
                //    [m].[Closed],
                //    [m].[Message],
                //    [m].[IsMarked],
                //    [m].[IsRead],
                //    [m].[ReadReplies]
                //FROM
                //    [messages] [m]
                //WHERE
                //    [m].[tid] = 0 AND [m].[mid] IN (4306367)
                #endregion
            }

            int updated =
                msgs
                    .Into(db.TopicInfos())
                        .Value(_ => _.MessageID, m => m.ID)
                        .Value(_ => _.ForumID, m => m.ForumID)
                        .Value(_ => _.AnswersCount, m => m.TopicAnswers.Count())
						.Value(
							_ => _.AnswersRates,
                            m =>
                                rates
                                    .Where(r => r.Message.TopicID == m.ID && r.RateType > 0)
                                    .Sum(r => (int)r.RateType * r.Multiplier))
						.Value(
							_ => _.AnswersUnread,
                            m => m.TopicAnswers.Count(im => !im.IsRead) + (m.IsRead ? 0 : 1))
						.Value(
							_ => _.AnswersSmiles,
                            m =>
                                rates
                                    .Count(r => r.Message.TopicID == m.ID && r.RateType == MessageRates.Smile))
						.Value(
							_ => _.AnswersAgrees,
                            m =>
                                rates
                                    .Count(r => r.Message.TopicID == m.ID && r.RateType == MessageRates.Agree))
						.Value(
							_ => _.AnswersDisagrees,
                            m =>
                                rates
                                    .Count(r => r.Message.TopicID == m.ID && r.RateType == MessageRates.DisAgree))
						.Value(
							_ => _.AnswersToMeUnread,
                            m =>
                                m
                                    .TopicAnswers
                                    .Count(im => !im.IsRead && im.Parent.UserID == selfId))
                        .Value(_ => _.AnswersMarked, m => m.TopicAnswers.Count(im => im.IsMarked))
                        .Value(_ => _.LastUpdateDate, m => m.TopicAnswers.Max(im => (DateTime?)im.Date) ?? m.Date)
						.Value(
							_ => _.AnswersModeratorials,
                            m =>
                                mods
                                    .Count(
                                        mod =>
                                            mod.Message.TopicID == m.ID
                                            && (mod.Message.LastModerated == null || mod.Create > mod.Message.LastModerated)))
                        .Value(_ => _.SelfRates, m => m.Rating())
                        .Value(_ => _.SelfSmiles, m => m.SmileCount())
                        .Value(_ => _.SelfAgrees, m => m.AgreeCount())
                        .Value(_ => _.SelfDisagrees, m => m.DisagreeCount())
                        .Value(_ => _.SelfModeratorials, m => m.ActiveModeratorialCount())
                    .Insert();
            #region SQL
            //DB INSERT INTO [topic_info] 
            //(
            //    1[mid],
            //    2[gid],
            //    3[answers_count],
            //    4[answers_rate],
            //    5[answers_unread],
            //    6[answers_smile],
            //    [answers_agree],
            //    [answers_disagree],
            //    [answers_me_unread],
            //    [answers_marked],
            //    [answers_last_update_date],
            //    [answers_mod_count],
            //    [this_rate],
            //    [this_smile],
            //    [this_agree],
            //    [this_disagree],
            //    [this_mod_count]
            //)
            //SELECT
            //    [m].[mid] as [ID],
            //    [m].[gid] as [ForumID],
            //    (
            //        SELECT
            //            Count(*)
            //        FROM
            //            [messages] [t1]
            //        WHERE
            //            [m].[mid] = [t1].[tid]
            //    ) as [c4],
            //    (
            //        SELECT
            //            Sum([r].[rate] * [r].[rby])
            //        FROM
            //            [rating] [r]
            //                INNER JOIN [messages] [t2] ON [r].[mid] = [t2].[mid]
            //        WHERE
            //            [t2].[tid] = [m].[mid] AND [r].[rate] > 0
            //    ) as [c5],
            //    (
            //        SELECT
            //            Count(*)
            //        FROM
            //            [messages] [im]
            //        WHERE
            //            [m].[mid] = [im].[tid] AND NOT ([im].[IsRead] = 1)
            //    ) + CASE
            //        WHEN [m].[IsRead] = 1 THEN 0
            //        ELSE 1
            //    END as [c6],
            //    (
            //        SELECT
            //            Count(*)
            //        FROM
            //            [rating] [r1]
            //                INNER JOIN [messages] [t3] ON [r1].[mid] = [t3].[mid]
            //        WHERE
            //            [t3].[tid] = [m].[mid] AND [r1].[rate] = -2
            //    ) as [c7],
            //    (
            //        SELECT
            //            Count(*)
            //        FROM
            //            [rating] [r2]
            //                INNER JOIN [messages] [t4] ON [r2].[mid] = [t4].[mid]
            //        WHERE
            //            [t4].[tid] = [m].[mid] AND [r2].[rate] = -4
            //    ) as [c8],
            //    (
            //        SELECT
            //            Count(*)
            //        FROM
            //            [rating] [r3]
            //                INNER JOIN [messages] [t5] ON [r3].[mid] = [t5].[mid]
            //        WHERE
            //            [t5].[tid] = [m].[mid] AND [r3].[rate] = 0
            //    ) as [c9],
            //    (
            //        SELECT
            //            Count(*)
            //        FROM
            //            [messages] [im1]
            //                LEFT JOIN [messages] [t6] ON [im1].[pid] = [t6].[mid]
            //        WHERE
            //            [m].[mid] = [im1].[tid] AND NOT ([im1].[IsRead] = 1) AND
            //            [t6].[uid] = @p1
            //    ) as [c10],
            //    (
            //        SELECT
            //            Count(*)
            //        FROM
            //            [messages] [im2]
            //        WHERE
            //            [m].[mid] = [im2].[tid] AND [im2].[IsMarked] = 1
            //    ) as [c11],
            //    Coalesce((
            //        SELECT
            //            Max([t7].[dte])
            //        FROM
            //            [messages] [t7]
            //        WHERE
            //            [m].[mid] = [t7].[tid]
            //    ), [m].[dte]) as [c12],
            //    (
            //        SELECT
            //            Count(*)
            //        FROM
            //            [moderatorials] [mod1]
            //                LEFT JOIN [messages] [t8] ON [mod1].[MessageID] = [t8].[mid]
            //        WHERE
            //            [t8].[tid] = [m].[mid] AND ([t8].[LastModerated] IS NULL OR [mod1].[Create] > [t8].[LastModerated])
            //    ) as [c13],
            //    (
            //        SELECT
            //            Sum(Cast([r4].[rate] as SmallInt) * [r4].[rby])
            //        FROM
            //            [rating] [r4]
            //        WHERE
            //            [m].[mid] = [r4].[mid] AND [r4].[rate] > 0
            //    ) as [c14],
            //    (
            //        SELECT
            //            Count(*)
            //        FROM
            //            [rating] [r5]
            //        WHERE
            //            [m].[mid] = [r5].[mid] AND [r5].[rate] = -2
            //    ) as [c15],
            //    (
            //        SELECT
            //            Count(*)
            //        FROM
            //            [rating] [r6]
            //        WHERE
            //            [m].[mid] = [r6].[mid] AND [r6].[rate] = -4
            //    ) as [c16],
            //    (
            //        SELECT
            //            Count(*)
            //        FROM
            //            [rating] [r7]
            //        WHERE
            //            [m].[mid] = [r7].[mid] AND [r7].[rate] = 0
            //    ) as [c17],
            //    CASE
            //        WHEN [m].[LastModerated] IS NULL
            //            THEN (
            //            SELECT
            //                Count(*)
            //            FROM
            //                [moderatorials] [t9]
            //            WHERE
            //                [m].[mid] = [t9].[MessageID]
            //        )
            //        ELSE (
            //            SELECT
            //                Count(*)
            //            FROM
            //                [moderatorials] [mod2]
            //            WHERE
            //                [m].[mid] = [mod2].[MessageID] AND [mod2].[Create] > [m].[LastModerated]
            //        )
            //    END as [c18]
            //FROM
            //    [messages] [m]
            //WHERE
            //    [m].[tid] = 0 AND [m].[mid] IN (4304177)
            #endregion
#if FRA_TRACE
            //Trace.WriteLine(string.Format("FRA: DB {0}\n>>>",db.LastQuery)); //MLHIDE
#endif
            return updated;
        }


        /// <summary>
        /// Updates the topic info.
        /// </summary>
        /// <param name="db">Менеджер баз данных.</param>
        /// <param name="tid">The tid.</param>
        /// <param name="selfId"></param>
        private static void UpdateTopicInfo(IDataContext db, int tid, int selfId)
        {
            UpdateTopicInfoByFilter(
                db,
                ti => ti.MessageID == tid,
                m => m.ID == tid, 
				selfId);
        }

        /// <summary>
        /// Updates the topic info range.
        /// </summary>
        /// <param name="provider">The provider.</param>
        /// <param name="db">Менеджер баз данных.</param>
        /// <param name="tids">The tids.</param>
        /// <param name="selfId">The self id.</param>
        /// <exception cref="ArgumentNullException"><paramref name="db"/> is <c>null</c>.</exception>
		public static void UpdateTopicInfoRange(
			IServiceProvider provider,	
			IDataContext db,
            IEnumerable<int> tids, 
            int selfId)
        {
            if (db == null)
            {
                throw new ArgumentNullException("db");                //MLHIDE
            }

            if (tids == null)
            {
                return;
            }

            IEnumerable<IEnumerable<int>> seriesContainer = tids.SplitToSeries(provider.MaxInClauseElements());

            foreach (IEnumerable<int> series in seriesContainer)
            {
                var locSeries = series;
                UpdateTopicInfoByFilter(
                    db,
                    ti => locSeries.Contains(ti.MessageID),
                    m => locSeries.Contains(m.ID),
					selfId);
            }
        }

        /// <summary>
        /// Updates the topic info specified.
        /// </summary>
        /// <param name="provider">The provider.</param>
        /// <param name="db">Менеджер баз данных.</param>
        /// <param name="affectedIds">The affected ids.</param>
        /// <param name="selfId"></param>
        public static void UpdateTopicInfoSpecified(
            IServiceProvider provider,
			IDataContext db,
            IEnumerable<int> affectedIds, 
            int selfId)
        {
#if FRM_UPDATE_TRACE
            Trace.WriteLine(string.Format("FRU: DataBaseManager.UpdateTopicInfoSpecified")); //MLHIDE
#endif

            foreach (IEnumerable<int> portion in affectedIds.SplitForInClause(provider))
            {
                IEnumerable<int> locPortion = portion;

                db
                    .TopicInfos(ti => locPortion.Contains(ti.MessageID))
                        .Set(_ => _.AnswersUnread, 0)
                        .Set(_ => _.AnswersToMeUnread, 0)
                    .Update();

                Table<IForumMessage> msgs = db.Messages();
                IQueryable<int> affectedTids =
                    msgs
                        .Where(m => locPortion.Contains(m.ID))
                        .Select(m => m.TopicID == 0 ? m.ID : m.TopicID)
                        .Distinct();

                var readCounts =
                    affectedTids
                        .Select(
                            tid =>
                                new
                                {
                                    TopicID = tid,
                                    UnreadCount =
                                        msgs
                                            .Where(m => !m.IsRead && m.TopicID == tid)
                                            .Count(),
                                    MeUnreadCount =
                                        msgs
                                            .Where(m => !m.IsRead && m.UserID == selfId && m.TopicID == tid)
                                            .Count()
                                })
                        .ToList();

                foreach (var cnt in readCounts)
                {
                    var locCnt = cnt;
                    db
                        .TopicInfos(ti => ti.MessageID == locCnt.TopicID)
                            .Set(_ => _.AnswersUnread, locCnt.UnreadCount)
                            .Set(_ => _.AnswersToMeUnread, locCnt.MeUnreadCount)
                        .Update();
                }
            }
        }

        /// <summary>
        /// Inits the db aggr.
        /// </summary>
        /// <param name="provider">The provider.</param>
        /// <param name="showGui">if set to <c>true</c> [show GUI].</param>
        /// <param name="msg">The MSG.</param>
        public static void InitDbAggr(
			IServiceProvider provider, 
			bool showGui, 
			string msg)
        {
            string s = !string.IsNullOrEmpty(msg) ? msg + "\n" : string.Empty; //MLHIDE

            //Now database will restructured for aggregates support. This may take several minutes.
            //Сейчас будет произведено обновление БД, обеспечивающее поддержку автоматического расчета агрегатных значений при синхронизации. Это может занять несколько минут.
            //SR.Database.WarningBeforeAggregateRestructure
            string text =
                string.Format(
                    s + ml.ml_string(163, "Сейчас будет произведено обновление БД, обеспечивающее поддержку автоматического расчета агрегатных значений при синхронизации. Это может занять несколько минут."),
                    ApplicationInfo.ApplicationName);

            DialogResult dialogResult = MessageBox.Show(
                text,
                ApplicationInfo.ApplicationName,
                MessageBoxButtons.OKCancel,
                MessageBoxIcon.Information);

            if (showGui && dialogResult == DialogResult.Cancel)
            {
                return;
            }

            ISharedInfoService sharedInfoService = provider.GetRequiredService<ISharedInfoService>();
            int selfId = sharedInfoService.GetIntValue(SharedInfoKeys.SelfId) ?? 0;

            using (IJanusDataContext db = provider.CreateDBContext())
            {
                using (IDbTransaction tx = db.BeginTransaction())
                {
                    Stopwatch stopwatch = Stopwatch.StartNew();

                    //db.TopicInfos().Delete();
                    int recordsProcessed = UpdateTopicInfoByFilter(db, null, null, selfId);
                    tx.Commit();

                    if (showGui)
                    {
                        //Restructure completed. Operation to take {0:0.000} second. Processed {1:### ### ##0} lines. Added {2:### ### ##0} themes.
                        //Обновление БД закончено. Процедура заняла {0:0.000} секунд. Обработано {1:### ### ##0} строк. Дополнено {2:### ### ##0} тем.
                        //SR.Database.WarningAfterAggregateRestructure
                        MessageBox.Show(
                            string.Format(
                                ml.ml_string(164, "Обновление БД закончено. Процедура заняла {0:0.000} секунд. Обработано {1:### ### ##0} строк. Дополнено {2:### ### ##0} тем."),
                                stopwatch.Elapsed.TotalSeconds,
                                recordsProcessed,
                                0),
                            ApplicationInfo.ApplicationName,
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
                    }
                }
            }
        }

        #endregion

        #region Работа с многозапросными транзакциями
        /// <summary>
        /// Clears the topic info.
        /// </summary>
        /// <param name="provider">The provider.</param>
        public static void ClearTopicInfo(IServiceProvider provider)
        {
			using (var db = provider.CreateDBContext())
            {
                db
                    .TopicInfos()
                    .Delete();
            }
        }

        /// <summary>
        /// Checks the topic info integrity.
        /// </summary>
        /// <param name="provider">The provider.</param>
        public static void CheckTopicInfoIntegrity(IServiceProvider provider)
        {
			using (var db = provider.CreateDBContext())
            {
                if (db.TopicInfos().Any())
                {
                    return;
                }

                if (!db.Messages().Any())
                {
                    return;
                }
            }

            // Таблица topic_info пустая, а messages не пустая.
            // Нужно обновить корневые метки.

            // SR.Database.NeedRestructure2
            // Damaged, changed or missing aggregates table.
            // Повреждена, изменена или отсутствует таблица подсчета агрегатных значений.
            InitDbAggr(provider, true, ml.ml_string(165, "Повреждена, изменена или отсутствует таблица подсчета агрегатных значений."));
        }
        #endregion

        public static bool IsValid(this IJanusDataContext db)
        {
            bool ret = true;
            //if (db.Connection != null)
            //{
            //    ret = !string.IsNullOrEmpty(db.Connection.ConnectionString);
            //}

            return ret;
        }
    }
}
